<?php
// 處室資料升級
class Default_Model_Upgrade_ScoreSs extends Default_Model_Upgrade_Core
{
    protected $_table = 'score_ss';
    protected $_targetTable = array('semester_subject','semester_course');

    function upgrade (){
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            // 先更改為 MYISAM
            $query = "ALTER TABLE `semester_subject` DROP FOREIGN KEY `semester_subject_ibfk_1` ; ";
            $this->_newdbPdo->exec($query);
            $query = "ALTER TABLE `semester_subject`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);
            $query = "ALTER TABLE `semester_course`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);


            //          $this->rawDbLink();
            //            $query = "CREATE TABLE IF NOT EXISTS `sfs3temp_score_ss` (
            //  `ss_id` smallint(5) unsigned NOT NULL auto_increment,
            //  `scope_id` tinyint(3) unsigned NOT NULL default '0',
            //  `subject_id` tinyint(3) unsigned NOT NULL default '0',
            //  `year` smallint(5) unsigned NOT NULL default '0',
            //  `semester` enum('1','2') NOT NULL default '1',
            //  `class_id` varchar(11) NOT NULL default '',
            //  `class_year` tinyint(4) unsigned NOT NULL default '0',
            //  `enable` enum('1','0') NOT NULL default '1',
            //  `need_exam` enum('1','0') NOT NULL default '1',
            //  `rate` tinyint(3) unsigned NOT NULL default '0',
            //  `sort` float default NULL,
            //  `sub_sort` tinyint(3) unsigned default NULL,
            //  `print` enum('1','0') default NUScoreSs.phpLL,
            //  `link_ss` varchar(200) NOT NULL default '',
            //  PRIMARY KEY  (`ss_id`),
            //  KEY `scope_id` (`scope_id`,`subject_id`,`year`),
            //  KEY `class_year` (`class_year`),
            //  KEY `sort` (`sort`),
            //  KEY `class_id` (`class_id`)
            //) ENGINE=MyISAM ";
            //            $this->_newdbPdo->exec($query);

            // 統計各學期班級數
            $query = "SELECT year, semester,c_year,count(*) AS cc FROM school_class GROUP BY year, semester,c_year";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            $scoreCountArr = array();
            foreach($res as $row) {
                $scoreCountArr[$row['year']][$row['semester']][$row['c_year']] = $row['cc'];
            }

            // 科目名稱
            $query = "SELECT subject_id,subject_name FROM score_subject";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            $subject_arr = array();
            foreach ($res as $row)
            $subject_arr[$row['subject_id']] = $this->iconv($row['subject_name']);
            // 記錄特殊班 ss_id
            $special_course_arr = array();

            $query = "SELECT * FROM score_ss ";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            $log = array();
            $success_count = 0;
            $error_count = 0;
            $rank = 0;

            foreach($res as $row){
                $arr = array();
                if ($row['link_ss']=='')
                $arr['scope'] = '非領域課程';
                else
                $arr['scope'] = $this->iconv($row['link_ss']);

                $arr['sn'] = $row['ss_id'];
                $arr['year'] = $row['year'];
                $arr['semester'] = $row['semester'];
                if ($row['subject_id'] == 0)
                $arr['name'] = $subject_arr[$row['scope_id']];
                else
                $arr['name'] = $subject_arr[$row['subject_id']];

                $arr['scope_rank'] = $row['sort'];
                $arr['rank'] = $row['sub_sort'];
                $arr['enable'] = ($row['enable']=='1')?'Y':'N';
                $arr['grade'] = $row['class_year'];
                if($arr['scope']=='彈性課程' or $arr['scope']=='非領域課程')
                $arr['required'] = 0;
                else
                $arr['required'] = 1;

                //                $find = $this->newdb->fetchRow("SELECT sn FROM semester_course
                //				  WHERE sn ='{$arr['sn']}'
                //				  ");
                $find = $this->_newdbPdo->query("SELECT sn FROM semester_course WHERE sn='{$arr['sn']}'");
                if ($find->rowCount() > 0)
                continue;
                if($row['class_id']== '') { // 年級課程
                    //                    $this->newdb->insert('semester_course',$arr);
                    $query  = "REPLACE INTO  semester_course (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                    $this->_newdbPdo->exec($query);
                    $arr = array();
                    $arr['year'] = $row['year'];
                    $arr['semester'] = $row['semester'];
                    $arr['grade'] =  $row['class_year'];
                    $arr['subject_sn'] = $row['ss_id'];
                    $arr['scored'] = ($row['need_exam']=='1')?'Y':'N';
                    $arr['scored_mode'] = $row['print'];
                    $arr['sections'] = $row['rate'];
                    $arr['rate'] = $row['rate'];
                    $arr['rank'] =  $row['sub_sort'];
                    $arr['enable'] = ($row['enable']=='1')?'Y':'N';

                    // 班級數
                    $cc = $scoreCountArr[$row['year']][$row['semester']][$row['class_year']];

                    for($i=1; $i<=$cc; $i++) {
                        $arr['class_sn'] = $i;
                        $query  = "REPLACE INTO  semester_subject (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);//or die($query);
                    }

                }
                else { // 特殊班課程
                    $subject_id = $row['ss_id'];
                    $query = "SELECT * FROM semester_course WHERE
                     year={$row['year']} AND semester={$row['semester']}
                     AND grade={$row['class_year']} AND scope='{$arr['scope']}' ";
                    $res = $this->_newdbPdo->query($query);//->fetchAll(PDO::FETCH_ASSOC);
                    //                    $res = $this->newdb->query($query)->fetch();
                    if ($res->rowCount()  > 0) {
                        $subject_id = $res['sn'];
                        $special_course_arr['s-'.$row['ss_id']] = $res['sn'];
                    }
                    else {
                        $arr['special'] = 'Y';
                        $arr['scope'] = '非領域課程';
                        $arr['required'] = '0';

                        $find = $this->_newdbPdo->query("SELECT year FROM semester_course
				  WHERE year='{$arr['year']}'
				  AND semester='{$arr['semester']}'
				  AND special='Y'
				  AND scope='非領域課程'
				  ");
                        if ($find->rowCount() == 0) {
                            //                        $this->newdb->insert('semester_course',$arr);
                            $query  = "REPLACE INTO  semester_course (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                            $this->_newdbPdo->exec($query);
                        }
                    }
                    $temp = explode("_",$row['class_id']);
                    $arr = array();
                    $arr['class_sn'] = (int)end($temp);
                    $arr['year'] = $row['year'];
                    $arr['semester'] = $row['semester'];
                    $arr['grade'] =  $row['class_year'];
                    $arr['subject_sn'] = $subject_id;
                    $arr['scored'] = ($row['need_exam']=='1')?'Y':'N';
                    $arr['scored_mode'] = $row['print'];
                    $arr['sections'] = $row['rate'];
                    $arr['rate'] = $row['rate'];
                    $arr['rank'] =  $row['sub_sort'];
                    $arr['enable'] = ($row['enable']=='1')?'Y':'N';

                    $find = $this->_newdbPdo->query("SELECT year FROM semester_subject
				  WHERE year='{$arr['year']}'
				  AND semester='{$arr['semester']}'
				  AND subject_sn='{$arr['subject_sn']}'
				  AND class_sn='{$arr['class_sn']}'
				  ");
                    if ($find->rowCount() == 0) {
                        //                    $this->newdb->insert('semester_subject',$arr)  ;
                        $query  = "REPLACE  INTO  semester_subject (".implode(',',array_keys($arr)).") VALUES('".implode("','",array_values($arr))."')";
                        $this->_newdbPdo->exec($query);
                    }
                }

            }

        }
        $query = "ALTER TABLE `semester_course`  ENGINE = INNODB";
        $this->_newdbPdo->exec($query);
        $query = "ALTER TABLE `semester_subject`  ENGINE = INNODB";
        $this->_newdbPdo->exec($query);
        $query = "ALTER TABLE `semester_subject` ADD FOREIGN KEY ( `subject_sn` ) REFERENCES  `semester_course` (
`sn`) ON DELETE CASCADE ON UPDATE CASCADE ; ";
        $this->_newdbPdo->exec($query);

        $log['special_course'] = $special_course_arr;
        $log['success_count'] = $success_count;
        $log['error_count'] = $error_count;
        $log['memo'] = 'SFS3 課程設定升級';
        $log['upgrade_time']	= date("Y-m-d h:i:s");

        $this->finishUpgrade($log);

    }

}